Business data is typically stored within physical tables of a database. The database may comprise a relational database, such as Oracle, Microsoft SQL Server, IBM DB2, Teradata and the like. Alternatively, the database could be a multi-dimensional database, an eXtendable Markup Language document, or any other structured data storage system.
The structures and relationships of the physical database tables are complex. A typical end user is therefore unable to locate or extract desired information from the physical database tables. Business Intelligence (BI) tools (e.g., BusinessObjects Universe Designer®) may be used to build an abstraction layer that shields end users from the complexity of the physical tables. More specifically, the abstraction layer allows the end users to query a database using intuitive terms rather than references to specific physical entities of the database.
U.S. Pat. No. 5,555,403 describes such an abstraction layer, referred to therein as a semantic layer. Briefly, a semantic layer defines a set of “business objects” that represent business entities, such as customers, time, financial figures, etc. Business objects may be classified as dimensions (along which one may want to perform an analysis or report), details (e.g., additional information on dimensions), and measures (e.g., indicators, most often numeric, whose value can be determined for a given combination of dimension values).
Dimension objects may be further abstracted into higher-level entities known as analysis objects. For example, the Country and City dimension objects may be child objects of a Geography analysis object, and a Product dimension object may be a child object of a Production analysis object. A dimension object may be referenced through its parent analysis object.
A user of a BI tool uses analysis objects of an abstraction layer to query underlying physical tables. Conventionally, the analysis objects (and their associated dimension objects) of an abstraction layer are considered orthogonal to one another. In other words, the abstraction layer provides the user with no indication of relationships between analysis objects (or dimension objects) which may actually exist in the underlying physical tables.
Microsoft SQL Server Analysis Services provide an abstraction layer including analysis objects (SSAS dimensions) and dimension objects (SSAS attributes). The Microsoft SQL Server Analysis Services abstraction layer allows declaration of functional dependencies between dimension objects (i.e., SSAS attributes), but only between the dimension objects that are associated with a same analysis object (i.e., SSAS dimension). Conversely, Microsoft SQL Server Analysis Services do not support functional dependencies between dimension objects (i.e., SSAS attributes) of different analysis objects (i.e., SSAS dimensions). Accordingly, as described above, analysis objects (i.e., SSAS dimensions) of Microsoft SQL Server Analysis Services are assumed to be orthogonal to one another.
FIG. 1 is a generic block diagram for further explaining an abstraction layer. Database 110, which may comprise any structured data storage, includes physical tables 115. Logical database schema 120 includes entities associated with some or all of physical tables 115, as well as additional entities, such as logical views and joins. Abstraction layer 130 includes business objects mapped to entities of logical database schema 120.
Consumer 140, which may comprise a reporting tool or any other system requiring access to the data of physical tables 110, views and interacts with the business objects (e.g., analysis objects, dimension objects) of abstraction layer 130. For example, consumer 140 may formulate a symbolic query using the business objects of abstraction layer 130. Query generator 150 may generate a query of database 110 based on the symbolic query and the mapping between logical database schema 120 and abstraction layer 130.
FIG. 2 illustrates database schema 200 of physical tables 115 according to one example. Schema 200 describes a database which stores information about customers who spend their holidays in resorts. Both the customers and the resorts are located in cities.
Database schema 200 presents a conventional “fan trap” problem because City table 210 is associated with several many-to-one relationships. If a business object name is mapped to the “City” table 210, and a user uses the business object to request “sales per city”, the request would be considered ambiguous. More specifically, the request would not specify whether the user is requesting the amount of sales per city of customers, or the amount of sales per city of resorts.
To address the foregoing, a designer of a conventional system creates a logical alias of each logical table that is a “fan trap”, and a logical alias of all tables to which the fan trap relates. Then, different user-friendly names will be associated to each of the aliased tables. For example, in view of schema 200, a designer using Business Objects' “Universe Designer” tool may create logical database schema 300 of FIG. 3, which includes logical aliases (310, 315) of City table 210 and logical aliases (320, 325) of Country table 220.
Creation of a logical alias does not require creation of corresponding physical tables or duplicate data. In the case of logical database schema 300, only one physical table remains for each of the City and Country tables of database schema 200, and each of the aliases is a logical view of one of these tables. The relationships from one aliased table to the other are properties of the alias, not of the physical table itself.
A designer of a corresponding abstraction layer may then simply associate a business object with each entity of the logical database schema. FIG. 4 is a block diagram of system 400 including database 410 of physical tables 415, which are assumed to conform to database schema 200. Logical database schema 420 is identical to logical database schema 300 and, as described above, abstraction layer 430 includes a business object associated with each entity of logical database schema 420.
Abstraction layer 430 allows a user to query, for instance, “sales by country of customer” without any ambiguity. However, abstraction layer 430 does not specify any functional dependencies between its dimension objects. Accordingly, abstraction layers such as abstraction layer 430 fail to provide a user with an intuitive understanding of the underlying relationships between their dimension objects.
What is needed is an efficient system to represent dependencies between analysis objects (and between dimension objects of different analysis objects) within an abstraction layer. Such a system may reduce a need to maintain complex static aliases or contexts, and may provide greater expressive power than current systems.